package DB;

import Model.Course;

import java.sql.*;
import java.util.ArrayList;

public class CourseDB implements CourseDBIF {

    private Connection conn;

    public CourseDB() {
        conn = ConnectionDB.getInstance().getConn();
    }

    @Override
    public int insertCourse(Course course) {
        int rc = -1;
        PreparedStatement pstmt = null;

        String insert = "INSERT INTO Course (courseId, name)" + "VALUES (?,?)";

        System.out.println(insert);

        try {
            pstmt = conn.prepareStatement(insert);
            pstmt.setInt(1, course.getId());
            pstmt.setString(2, course.getName());
            rc = pstmt.executeUpdate();
            pstmt.close();
        }
        catch (SQLException sqlE) {
            System.out.println("SQL Error: " + sqlE.getMessage());
        }
        catch (Exception e) {
            e.getMessage();
        }

        return rc;
        
    }

    @Override
    public Course findCourse(int id) {
        return singleWhere("courseId = " + id, false);
    }

    @Override
    public ArrayList<Course> getAllCourses(boolean retrieveAssociation) {
        return miscWhere("", false);
    }

    @Override
    public int updateCourse(Course course) {
        Course courseObj = course;
        int rc = -1;

        String query="UPDATE Course SET " +
                "name ='" +courseObj.getName() + "' " +
                " WHERE courseId = " + courseObj.getId() + "";

        System.out.println("Update query: " + query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception e){
            System.out.println("Update exception in Course db: " +e);
        }
        return rc;
    }

    @Override
    public int deleteCourse(int id) {
        int rc = -1;

        String query = "DELETE FROM Course WHERE courseId = '" + id + "'";

        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception e){
            System.out.println("Delete exception in course db: " + e);
        }
        return(rc);
    }

    //Finding a single Course
    private Course singleWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        Course courseObj = new Course();

        String query = buildQuery(wClause);
        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next()) {
                courseObj = buildCourse(results);
                stmt.close();
            }
            else {
                courseObj = null;
            }
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
        }

        return courseObj;
    }

    //Finding all Courses
    private ArrayList<Course> miscWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        ArrayList<Course> list = new ArrayList<Course>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()) {
                Course courseObj = new Course();
                courseObj = buildCourse(results);
                list.add(courseObj);
            }

            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
            e.printStackTrace();
        }

        return list;
    }

    //Method to build the query
    private String buildQuery(String wClause) {
        String query = "SELECT courseId, name FROM Course ";

        if (wClause.length()>0) {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    //Method to build a Course object
    private Course buildCourse(ResultSet results) {

        Course courseObj = new Course();

        try {
            courseObj.setId(results.getInt("courseId"));
            courseObj.setName(results.getString("name"));
        }
        catch (Exception e) {
            System.out.println("Error building Course object");
        }

        return courseObj;                          
    }

    public int courseGetMax() {
        int nextId = GetMax.getMaxId("SELECT max(courseId) from Course");

        nextId = nextId +1;

        return nextId;
    }

}
